﻿/*
*OracleConfigurature .cs
*
* 功 能： 本地oracle数据库导入到服务器数据库,连接本地数据库和从本地库获得数据
* 类 名： OracleConfigurature
*
* Ver    变更日期  20130803   负责人  黄永生  变更内容
* ───────────────────────────────────

*/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;
using System.Web.UI.WebControls;
using System.Xml;
using System.IO;
using System.Collections;
using DQYT.HTDZ.Common;

namespace DQYT.HTDZ.DBUtility
{
    public class OracleConfigurature : IConfigurature
    {
        private string conStr = null;//连接本地库连接字符串
        private OracleConnection connection = null;
        private DataTable dataTable = new DataTable();//保存获得的本地数据库数据
        private string[] restrictStr = new string[2];
        private string[] info = null;
        private string srcTableName = null;
        private GridView gridView;
        private string filePath ;//= "System.Windows.Forms.Application.StartupPath"+"\\oracle_conf.xml";
        private List<ArrayList> listArrayList;

        /// <summary>
        /// 构造连接字符串
        /// </summary>
        /// <param name="confInfo"></param>
        /// <param name="FilePath"></param>
        public OracleConfigurature(string[] confInfo,string FilePath)
        {
            conStr = "Data Source=(DESCRIPTION="
                                 + "(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=" + confInfo[0] + ")(PORT=" + confInfo[1] + ")))"
                                 + "(CONNECT_DATA=(SERVER=DEDICATED)(SID=" + confInfo[2] + ")));User ID="
                                 + confInfo[3] + ";Password=" + confInfo[4] + ";";

            //conStr = "user id="+ confInfo[3] +"; data source="+ confInfo [2] +"_" + confInfo[0]+";password="+ confInfo[4] ;

            restrictStr[0] = confInfo[3].ToUpper(); 
            connection = new OracleConnection(conStr);
            info = confInfo;

            this.filePath = FilePath;
        }

        #region IConfigurature Members

        /// <summary>
        /// 获得所某数据表所有数据列
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public List<string> GetColumnsScama(string tableName)
        {
           srcTableName = tableName;
           dataTable.Columns.Clear();
           restrictStr[1] = tableName;
           List<string> comlis = new List<string>();
           connection.Open();
           try
           {
               dataTable = connection.GetSchema("columns", restrictStr);
               DataRowCollection rows = dataTable.Rows;
               foreach (DataRow row in rows)
               {
                   comlis.Add(row[2].ToString());
               }
           }

           finally
           {
               connection.Close();
           }
           return comlis;
        }
        /// <summary>
        /// 获得所有数据表名称
        /// </summary>
        /// <returns></returns>
        public List<string> GetTableScama()
        {
            dataTable.Columns.Clear();
            List<string> lis = new List<string>();
            restrictStr[1] = null;
            connection.Open();
            try
            {
                dataTable = connection.GetSchema("tables", restrictStr);
                DataRowCollection rows = dataTable.Rows;
                foreach (DataRow row in rows)
                {
                    lis.Add(row[1].ToString());
                }
 
            }
            finally
            {
                connection.Close();
            }
            return lis; 
        }
        /// <summary>
        /// 获得查询结果集,返回一个DataTable
        /// </summary>
        /// <param name="queryStr"></param>
        /// <returns></returns>
        public DataTable GetQueryResultSet(string queryStr)
        {
            DataSet ds = new DataSet();
            OracleCommand command = new OracleCommand(queryStr, connection);
            OracleDataAdapter adapter = new OracleDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
                JsAlert.Alert("数据库连接错误");
            }
            return ds.Tables[0];
        }
        /// <summary>
        ///获得查询结果集,返回一个DataSet
        /// </summary>
        /// <param name="queryStr"></param>
        /// <returns></returns>
        public DataSet  GetQueryResultDataSet(string queryStr)
        {
            DataSet ds = new DataSet();
            OracleCommand command = new OracleCommand(queryStr, connection);
            OracleDataAdapter adapter = new OracleDataAdapter(command);
            try
            {
                adapter.Fill(ds);
            }
            catch
            {
                JsAlert.Alert("数据库连接错误");
            }
            return ds;
        }
       /// <summary>
        /// 保存配置文件
       /// </summary>
       /// <param name="d"></param>
        public void SaveConf(GridView d)
        {
            this.gridView = d;
            FileInfo confFile = new FileInfo(filePath);
            if (!confFile.Exists)
            {
                CreateOracleConfFile(filePath);
            }
            else
            {
                ModifyOracleConfFile(filePath);
            }
            JsAlert.Alert("配置文件已保存！");
        }
        /// <summary>
        /// 保存配置信息
        /// </summary>
        /// <param name="laList">字段对应数据</param>
        public void SaveConf(List<ArrayList> laList)
        {
            this.listArrayList = laList;
            FileInfo confFile = new FileInfo(filePath);
            if (confFile.Exists)
            {
                confFile.Delete();   
            }
            CreateOracleConfFile_List(filePath);
            JsAlert.Alert("配置文件已保存！");
        }

        /// <summary>
        /// 加载本地数据库库与服务器数据库的配置文件
        /// </summary>
        /// <param name="tableSelectBox"></param>
        /// <param name="dgv"></param>
        public void LoadConf(DropDownList tableSelectBox,GridView dgv)
        {
            this.gridView = dgv;
            FileInfo confFile = new FileInfo(filePath);
            if (!confFile.Exists)
            {
                JsAlert.Alert("配置文件不存在");
            }
            else
            {
                bool nodeContained = false;
                XmlDocument doc = new XmlDocument();
                doc.Load(filePath);
                XmlNodeList tableList = doc.GetElementsByTagName("Table");

                foreach (XmlNode node in tableList)
                {
                    if (node.Attributes["des_table"].Value.ToString() == info[5])
                    {
                        nodeContained = true;
                        //tableSelectBox.SelectedItem = node.Attributes["src_table"].Value.ToString();
                        tableSelectBox.SelectedValue = node.Attributes["src_table"].Value.ToString();
                        LoadFieldMapping(node);
                        break;
                    }
                }

                if (!nodeContained)
                {
                    JsAlert.Alert("配置文件中没有保存该表的字段映射，请重新配置后保存");
                }
            }
        }

        #endregion

        /// <summary>
        /// SAX方式写入XML
        /// </summary>
        /// <param name="fileName"></param>
        private void CreateOracleConfFile(string fileName)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            //settings.NewLineOnAttributes = true;
            XmlWriter writer = XmlWriter.Create(fileName, settings);
            writer.WriteStartDocument(); //xml声明

            writer.WriteStartElement("Oracle-Conf");//根节点开始

            //数据库连接配置
            writer.WriteAttributeString("host_name", info[0]);
            writer.WriteAttributeString("port_number", info[1]);
            writer.WriteAttributeString("instance_name", info[2]);
            writer.WriteAttributeString("user_name", info[3]);
            writer.WriteAttributeString("password", Ecode(info[4]));

            //数据库映射配置 
            writer.WriteStartElement("Tables");//tables节点开始

            writer.WriteStartElement("Table");//table节点开始
            writer.WriteAttributeString("des_table", info[5]);
            writer.WriteAttributeString("src_table", srcTableName);

            //设置表字段映射
            int rowCount = this.gridView.Rows.Count;
            int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                writer.WriteStartElement("Field");//字段节点开始
                writer.WriteAttributeString("des_field", this.gridView.Rows[i].Cells[2].Text.ToString());//this.gridView[2, i].Value.ToString());

                DropDownList ddl = new DropDownList();
                ddl = (DropDownList)(this.gridView.Rows[i].FindControl("ddlGridView"));
                value = ddl.SelectedValue.ToString();
                
                if (value == null)
                {
                    writer.WriteAttributeString("src_field", "");
                }
                else
                {
                    writer.WriteAttributeString("src_field", value.ToString());
                }
                writer.WriteEndElement();//字段节点结束
            }

            writer.WriteEndElement();//table节点结束
            writer.WriteEndElement();//tables节点结束
            writer.WriteEndElement();//根节点结束
            writer.WriteEndDocument();


            writer.Flush();
            writer.Close();

        }
        /// <summary>
        /// 新建oracle数据库连接配置文件采用list数据
        /// </summary>
        /// <param name="fileName">配置文件名</param>
        private void CreateOracleConfFile_List(string fileName)
        {
            XmlWriterSettings settings = new XmlWriterSettings();
            settings.Indent = true;
            //settings.NewLineOnAttributes = true;
            XmlWriter writer = XmlWriter.Create(fileName, settings);
            writer.WriteStartDocument(); //xml声明

            writer.WriteStartElement("Oracle-Conf");//根节点开始

            //数据库连接配置
            writer.WriteAttributeString("host_name", info[0]);
            writer.WriteAttributeString("port_number", info[1]);
            writer.WriteAttributeString("instance_name", info[2]);
            writer.WriteAttributeString("user_name", info[3]);
            writer.WriteAttributeString("password", Ecode(info[4]));

            //数据库映射配置 
            writer.WriteStartElement("Tables");//tables节点开始

            writer.WriteStartElement("Table");//table节点开始
            writer.WriteAttributeString("des_table", info[5]);
            writer.WriteAttributeString("src_table", srcTableName);

            //设置表字段映射
            int rowCount = this.listArrayList.Count; //this.gridView.Rows.Count;
            //int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                writer.WriteStartElement("Field");//字段节点开始
                writer.WriteAttributeString("des_field", this.listArrayList[i][0].ToString());
                value=this.listArrayList[i][1].ToString();

                if (value == null)
                {
                    writer.WriteAttributeString("src_field", "");
                }
                else
                {
                    writer.WriteAttributeString("src_field", value.ToString());
                }
                writer.WriteEndElement();//字段节点结束
            }

            writer.WriteEndElement();//table节点结束
            writer.WriteEndElement();//tables节点结束
            writer.WriteEndElement();//根节点结束
            writer.WriteEndDocument();


            writer.Flush();
            writer.Close();

        }

        /// <summary>
        ///DOM方式检索，保存XML 
        /// </summary>
        /// <param name="fileName"></param>
        private void ModifyOracleConfFile(string fileName)
        {
            bool nodeContained = false;
            XmlDocument doc = new XmlDocument();
            doc.Load(fileName);
            XmlNodeList oraNodes = doc.GetElementsByTagName("Oracle-Conf");

            if (oraNodes.Count > 0)
            {
                XmlNode oraNode = oraNodes[0];
                XmlAttributeCollection attributes = oraNode.Attributes;
                attributes["host_name"].Value = info[0];
                attributes["port_number"].Value = info[1];
                attributes["instance_name"].Value = info[2];
                attributes["user_name"].Value = info[3];
                attributes["password"].Value = Ecode(info[4]);

                XmlNodeList tableList = doc.GetElementsByTagName("Table");

                foreach (XmlNode node in tableList)
                {
                    if (node.Attributes["des_table"].Value.ToString() == info[5])
                    {
                        node.RemoveAll();
                        AddFieldMapingNode(doc, node);
                        nodeContained = true;
                        break;
                    }
                }

                if (!nodeContained)
                {
                    XmlNode tableNode = doc.CreateNode("element", "Table", null);
                    XmlNodeList ts = doc.GetElementsByTagName("Tables");
                    if (ts.Count > 0)
                    {
                        ts[0].AppendChild(tableNode);
                    }
                    AddFieldMapingNode(doc, tableNode);
                    nodeContained = false;
                }

                doc.Save(fileName);
            }
        }

        /// <summary>
        /// 为表添加字段映射节点
        /// </summary>
        /// <param name="doc"></param>
        /// <param name="node"></param>
        private void AddFieldMapingNode(XmlDocument doc, XmlNode node)
        {
            node.Attributes.Append(doc.CreateAttribute("des_table"));
            node.Attributes.Append(doc.CreateAttribute("src_table"));
            node.Attributes["des_table"].Value = info[5];
            node.Attributes["src_table"].Value = srcTableName;

            int rowCount = this.gridView.Rows.Count;
            int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                XmlElement element = doc.CreateElement("Field");
                element.SetAttribute("des_field", this.gridView.Rows[i].Cells[2].Text.ToString());// this.gridView[2, i].Value.ToString());
                value = ((DropDownList)(this.gridView.Rows[i].FindControl("ddlGridView"))).SelectedValue.ToString(); // this.gridView.Rows[i].Cells[colNumber].Text.ToString();// this.gridView[colNumber, i].Value;
                
                if (value == null)
                {
                    element.SetAttribute("src_field", "");
                }
                else
                {
                    element.SetAttribute("src_field", value.ToString());
                }
                node.AppendChild(element);
            }
        }
        /// <summary>
        /// 表?字段映射节点
        /// </summary>
        /// <param name="tableNode"></param>
        private void LoadFieldMapping(XmlNode tableNode)
        {
            XmlNodeList tableList = tableNode.ChildNodes;
            Hashtable ht = new Hashtable();
            foreach (XmlNode node in tableList)
            {                               
                ht.Add(node.Attributes["des_field"].Value.ToString(), node.Attributes["src_field"].Value.ToString());
            }
            List<string> ls = GetColumnsScama(tableNode.Attributes["src_table"].Value.ToString()); 
            int rowCount = this.gridView.Rows.Count;
            int colNumber = this.gridView.Columns.Count - 1;
            object value = null;
            for (int i = 0; i < rowCount; i++)
            {
                //value = ht[this.gridView[2, i].Value.ToString()];
                value = ht[this.gridView.Rows[i].Cells[1].Text.ToString()];

                if (value.ToString() == "")
                {
                    //this.gridView[colNumber, i].Value = null;
                    this.gridView.Rows[i].Cells[colNumber].Text = null;
                }
                else if (ls.Contains(value.ToString()))
                {
                    //this.gridView[colNumber, i].Value = value;
                    DropDownList ddl = (DropDownList)this.gridView.Rows[i].FindControl("DropDownList1");
                    ddl.SelectedValue = value.ToString();
                }
                else
                {
                    //this.gridView[colNumber, i].Value = null;
                    DropDownList ddl = (DropDownList)this.gridView.Rows[i].FindControl("DropDownList1");
                    ddl.SelectedValue = "";
                }
            }
        }

        /// <summary>
        /// 编码
        /// </summary>
        /// <param name="password"></param>
        /// <returns></returns>
        public static string Ecode(string password)
        {
            string pass = "";
            for (int i = 0; i < password.Length; i++)
            {
                pass = pass + (char)(password[i] + 10 - i * 2);
            }
            return pass;
        }

        /// <summary>
        /// 解码
        /// </summary>
        /// <param name="password"></param>
        /// <returns></returns>
        public static  string Dcode(string password)
        {
            string pass = "";
            for (int i = 0; i < password.Length; i++)
            {
                pass = pass + (char)(password[i] - 10 + i * 2);
            }
            return pass;
        }
    }
}
